<?php

class mdl_t_surat_keluar extends CI_Model{

	function __construct(){
		parent::__construct();
	}

	function getData($num=0, $offset=0){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('t_surat_keluar');
		$this->db->limit($num, $offset);
		$this->db->order_by('id');

		return $this->db->get();

	}

	function update($data){
		$this->db->flush_cache();

		$this->db->set('no_agenda', $data['no_agenda']);

		$this->db->where('id', $data['id']);

		$result = $this->db->update('t_surat_keluar');

		if($result) {
			return TRUE;
		}else {
			return FALSE;
		}
	}

	function get_item_by_id($num=0, $offset=0, $userid){
		$this->db->flush_cache();
		$this->db->select('t_surat_keluar.*');
		$this->db->from('t_surat_keluar2');
		$this->db->join('log_surat_keluar', 't_surat_keluar.id=log_surat_keluar.id_surat','left');
		$this->db->where('t_surat_keluar.kirim_ke', $userid);
		$this->db->where('t_surat_keluar.kirim_ke', $userid);
		$this->db->limit($num, $offset);
		$this->db->order_by('t_surat_keluar.id');
		return $this->db->get();
	}

	function get_surat_keluar($num=0, $offset=0, $userid){
		$this->db->flush_cache();
		$this->db->select('t_surat_keluar.*');
		$this->db->from('t_surat_keluar');
		$this->db->where('t_surat_keluar.pengolah', $userid);
		$this->db->or_where('t_surat_keluar.kirim_ke', $userid);
		$this->db->limit($num, $offset);
		$this->db->order_by('t_surat_keluar.id');
		return $this->db->get();
	}

	/*function get_surat_keluar_n_log($num=0, $offset=0, $data){
		$this->db->flush_cache();
		$this->db->select('a.no_agenda,b.id_surat,a.id,a.gen_log_surat,a.kode,a.tujuan,a.no_surat,a.tgl_surat,b.kirim_ke,b.userid,b.isi_surat as isi_ringkas,b.id as id_log,b.is_revision');
		$this->db->from('t_surat_keluar a');
		$this->db->join('log_surat_keluar b','a.gen_log_surat=b.gen_log_surat');


		if (isset($data['kirim_ke']) && $data['kirim_ke']!=''){
			$this->db->where('b.kirim_ke', $data['kirim_ke']);
		}

		if(isset($data['userid']) &&  $data['userid']!=''){
			$this->db->or_where('b.userid', $data['userid']);
		}

		//$this->db->group_by('b.gen_log_surat');


		$this->db->order_by('b.id_surat');
		$this->db->order_by('b.date');
		$this->db->order_by('b.time');
		return $this->db->get();
	}*/
	function get_surat_keluar_n_log($num=0, $offset=0, $data){
		$this->db->flush_cache();


		$this->db->select('a.no_agenda,b.id_surat,a.id,a.gen_log_surat,a.kode,a.tujuan,a.no_surat,a.tgl_surat,b.kirim_ke,b.userid,b.isi_surat as isi_ringkas,b.id as id_log,b.is_revision');
		$this->db->from('t_surat_keluar a');
		$this->db->join('log_surat_keluar b','a.gen_log_surat=b.gen_log_surat');


		// if (isset($data['kirim_ke']) && $data['kirim_ke']!=''){
		// 	$this->db->where('b.kirim_ke', $data['kirim_ke']);
		// }

		// if(isset($data['userid']) &&  $data['userid']!=''){
		// 	$this->db->or_where('b.userid', $data['userid']);
		// }

		//$this->db->group_by('b.gen_log_surat');
		$this->db->where("b.id IN (SELECT MAX( id )
		FROM log_surat_keluar
		WHERE kirim_ke = '" . $data['kirim_ke'] . "'
		OR userid = '". $data['userid'] ."'
		GROUP BY gen_log_surat)", NULL, FALSE);

		$this->db->order_by('b.id_surat');
		$this->db->order_by('b.date');
		$this->db->order_by('b.time');
		return $this->db->get();
	}

	function get_surat_keluar_n_log_by_id($num=0, $offset=0, $data){
		$this->db->flush_cache();
		$this->db->select('a.no_agenda,b.id_surat,a.id,a.gen_log_surat,a.kode,a.tujuan,a.no_surat,a.tgl_surat,b.kirim_ke,b.userid,b.isi_surat as isi_ringkas,b.id as id_log');
		$this->db->from('t_surat_keluar a');
		$this->db->join('log_surat_keluar b','a.gen_log_surat=b.gen_log_surat');
		if (isset($data['id']) && $data['id']!=''){
			$this->db->where('b.id', $data['id']);
		}

		//$this->db->group_by('b.gen_log_surat');

		if ($offset!=0){
			$this->db->limit($num, $offset);
		}

		$this->db->order_by('b.id_surat');
		$this->db->order_by('b.date');
		$this->db->order_by('b.time');
		return $this->db->get();
	}

	function get_surat_keluar_n_log_by_id_surat($num=0, $offset=0, $data){
		$this->db->flush_cache();
		$this->db->select('a.no_agenda,b.id_surat,a.id,a.gen_log_surat,a.kode,a.tujuan,a.no_surat,a.tgl_surat,b.kirim_ke,b.userid,b.isi_surat as isi_ringkas,b.id as id_log');
		$this->db->from('t_surat_keluar a');
		$this->db->join('log_surat_keluar b','a.gen_log_surat=b.gen_log_surat');
		if (isset($data['id_surat']) && $data['id_surat']!=''){
			$this->db->where('b.id_surat', $data['id_surat']);
		}
		if (isset($data['gen_log_surat']) && $data['gen_log_surat']!=''){
			$this->db->where('b.gen_log_surat', $data['gen_log_surat']);
		}

		//$this->db->group_by('b.gen_log_surat');

		if ($offset!=0){
			$this->db->limit($num, $offset);
		}

		$this->db->order_by('b.id_surat');
		$this->db->order_by('b.date');
		$this->db->order_by('b.time');
		return $this->db->get();
	}

	function get_gen_kode()
	{
		# ambil nomer running berdasarkan
		$this->db->flush_cache();
		$this->db->select('max(id)');
		$this->db->from('t_surat_keluar');
		$query = $this->db->get();

		$gen_kode = $query->num_rows();
		$gen_kode = (int) $gen_kode + 1;
		$gen_kode = str_pad($gen_kode, 4, '0', STR_PAD_LEFT);

		return 'SK'. date('Ymdhis') .$gen_kode;

	}


	function insert($data){
		$this->db->flush_cache();
		$this->db->set('kode', $data['kode']);
		$this->db->set('no_agenda', $data['no_agenda']);
		$this->db->set('indek_berkas', $data['indek_berkas']);
		$this->db->set('isi_ringkas', $data['uraian']);
		$this->db->set('dari', $data['dari']);
		$this->db->set('no_surat', $data['no_surat']);
		$this->db->set('tgl_surat', $data['tgl_surat']);
		$this->db->set('tgl_diterima', $data['tgl_diterima']);
		$this->db->set('kpd_yth',  $data['kpd_yth']);
		$this->db->set('keterangan',  $data['keterangan']);
		$this->db->set('file',  $data['file']);
		$this->db->set('pengolah',  $data['pengolah']);
		$this->db->set('is_surat_masuk',  $data['is_surat_masuk']);
		//$this->db->set('status',  $data['status']);
		$this->db->set('userid',  $data['userid']);

		$result = $this->db->insert('t_surat_masuk');

		if($result) {
			return TRUE;
		}else {
			return FALSE;
		}
	}

	function getDataEdit($id){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('t_surat_masuk');
		$this->db->where('id', $id);

		return $this->db->get();
	}



	function delete($data){
		$this->db->flush_cache();
		$this->db->where('id', $data['id']);
		$result = $this->db->delete('t_surat_masuk');

		if($result) {
			return TRUE;
		}else {
			return FALSE;
		}

	}

	function get_union_table_all_row($awal, $akhir,$arr_where='', $get_count=false){
	    $limit_row = "";
	    if ($get_count==true){
	    	$limit_row = "LIMIT $awal, $akhir";
	    }
	    if ($arr_where!=''){

	    }

	    $query = $this->db->query("SELECT id,kode,no_agenda,isi_ringkas,tujuan,ori_kirim_ke,no_surat, pengolah, tgl_surat, log_kirim_ke,userid,date,time
								FROM (
								SELECT a.id,a.kode,a.no_agenda,a.isi_ringkas,a.tujuan,a.kirim_ke as ori_kirim_ke,a.no_surat,a.pengolah,a.tgl_surat,b.kirim_ke  as log_kirim_ke,b.userid,b.date,b.time
								FROM t_surat_keluar a
								INNER JOIN log_surat_keluar b ON a.id = b.id_surat
								UNION ALL
								SELECT c.id,c.kode,c.no_agenda,c.isi_ringkas,c.tujuan,c.kirim_ke as ori_kirim_ke,c.no_surat,c.pengolah,c.tgl_surat,'', '','',''
								FROM t_surat_keluar c
								)tbl
	    						" . $arr_where . "
								ORDER BY id DESC, date, time
								" . $limit_row);

	    return $query;

	}

	function get_union_table_inner($awal, $akhir,$arr_where='', $get_count=false){
	    $limit_row = "";
	    if ($get_count==true){
	    	$limit_row = "LIMIT $awal, $akhir";
	    }
	    if ($arr_where!=''){

	    }

	    $query = $this->db->query("SELECT id, id_disposisi, is_surat_masuk,kode,no_agenda,isi_ringkas,file,dari,tgl_surat,no_surat,status,id_status_disposisi
								FROM (
								SELECT c.id_surat AS id, c.id_disposisi, c.is_surat_masuk,a.kode,a.no_agenda,a.isi_ringkas,a.file,a.dari,a.tgl_surat,a.no_surat,a.status,c.id as id_status_disposisi
								FROM t_surat_masuk a
								INNER JOIN t_disposisi b ON a.id = b.id_surat
								LEFT JOIN t_status_disposisi c ON b.id_disposisi = c.id_disposisi
								UNION ALL
								SELECT a.id, '', a.is_surat_masuk,a.kode,a.no_agenda,a.isi_ringkas,a.file,a.dari,a.tgl_surat,a.no_surat,a.status,''
								FROM t_surat_masuk a
								)tbl
	    						" . $arr_where . "
								ORDER BY id, is_surat_masuk DESC
								" . $limit_row);

	    return $query;

	}

	function get_detail($data){
		$this->db->flush_cache();
		$this->db->select('a.id AS id, c.id_disposisi, c.is_surat_masuk,a.kode,a.no_agenda,a.isi_ringkas,a.file,a.dari,a.tgl_surat,a.no_surat,a.status');
		$this->db->from('t_surat_masuk a');
		$this->db->join('t_disposisi b','a.id = b.id_surat','left');
		$this->db->join('t_status_disposisi c','b.id_disposisi = c.id_disposisi','left');
		$this->db->where('a.id', $data['id_surat']);
		if ($data['id_disposisi']!='0'){
			$this->db->where('c.id_disposisi', $data['id_disposisi']);
		}
		if ($data['id_status_disposisi']!='0'){
			$this->db->where('c.id', $data['id_status_disposisi']);
		}


		return $this->db->get();
	}

	function get_detail_id($num=0, $offset=0, $data){
		$this->db->flush_cache();
		$this->db->select('a.no_agenda,b.id_surat,a.id,a.gen_log_surat,a.kode,a.tujuan,a.no_surat,a.tgl_surat,b.kirim_ke,b.userid,b.isi_surat as isi_ringkas,b.id as id_log,a.keterangan,a.tgl_catat');
		$this->db->from('t_surat_keluar a');
		$this->db->join('log_surat_keluar b','a.gen_log_surat=b.gen_log_surat');
		if (isset($data['id']) && $data['id']!=''){
			$this->db->where('b.id', $data['id']);
		}


		//$this->db->group_by('b.gen_log_surat');

		if ($offset!=0){
			$this->db->limit($num, $offset);
		}

		$this->db->order_by('b.id_surat');
		$this->db->order_by('b.date');
		$this->db->order_by('b.time');
		return $this->db->get();
	}



}
?>